package com.nk.web;

import com.nk.domain.AjaxRes;
import com.nk.domain.Employee;
import com.nk.domain.PageListRes;
import com.nk.domain.QueryVo;
import com.nk.service.EmployeeService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.util.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Controller
public class ExcelController {

    @Autowired
    private EmployeeService employeeService;

    // 导出数据
    @RequestMapping("downExcel")
    @ResponseBody
    public void downExcel(HttpServletResponse response){

        try {

            QueryVo queryVo = new QueryVo();
            queryVo.setPage(1);
            queryVo.setRows(10);
            PageListRes pageListRes = employeeService.getEmployeeList(queryVo);
            List<Employee> employeeList = (List<Employee>) pageListRes.getRows();
            // 创建excel
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建Excel中的sheet
            HSSFSheet sheet = workbook.createSheet("员工数据");
            // 创建第一行
            HSSFRow row = sheet.createRow(0);
            // 创建第一行的每一列
            row.createCell(0).setCellValue("编号");
            row.createCell(1).setCellValue("姓名");
            row.createCell(2).setCellValue("入职时间");
            row.createCell(3).setCellValue("电话");
            row.createCell(4).setCellValue("邮箱");

            HSSFRow employeeRow = null;
            for (int i = 0; i < employeeList.size(); i++){
                Employee employee = employeeList.get(i);
                employeeRow = sheet.createRow(i + 1);
                employeeRow.createCell(0).setCellValue(employee.getId());
                employeeRow.createCell(1).setCellValue(employee.getUsername());
                if (employee.getInputtime() != null){
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String date = simpleDateFormat.format(employee.getInputtime());
                    employeeRow.createCell(2).setCellValue(date);
                }else {
                    employeeRow.createCell(2).setCellValue("");
                }
                employeeRow.createCell(3).setCellValue(employee.getTel());
                employeeRow.createCell(4).setCellValue(employee.getEmail());
            }

            //3.响应给浏览器
            String fileName = new String("员工数据.xls".getBytes("utf-8"), "iso8859-1");
            response.setHeader("content-Disposition","attachment;filename="+fileName);
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 下载Excel模板
    @RequestMapping("downloadExcelTpl")
    public void downloadExcelTpl(HttpServletRequest request,HttpServletResponse response){

        FileInputStream inputStream = null;

        try {
            String fileName = new String("ExcelTml.xls".getBytes("utf-8"), "iso8859-1");
            response.setHeader("content-Disposition","attachment;filename="+fileName);
            String realPath = request.getSession().getServletContext().getRealPath("static/ExcelTml.xls");
            inputStream = new FileInputStream(realPath);
            IOUtils.copy(inputStream,response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (inputStream != null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 上传Excel
    @RequestMapping("uploadExcelFile")
    @ResponseBody
    public AjaxRes uploadExcelFile(MultipartFile excel){

        AjaxRes ajaxRes = new AjaxRes();
        try {
            ajaxRes.setSuccess(true);
            ajaxRes.setMessage("上传成功");

            HSSFWorkbook workbook = new HSSFWorkbook(excel.getInputStream());
            HSSFSheet sheet = workbook.getSheetAt(0);
            // 获取最后一行
            int lastRowNum = sheet.getLastRowNum();
            System.out.println("00000000000000000000000"+lastRowNum);
            Row employeeRow = null;
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (int i = 1; i <= lastRowNum; i++) {
                Employee employee = new Employee();
                employeeRow = sheet.getRow(i);

//                Double id = (Double) getCellValue(employeeRow.getCell(0));
//                employee.setId(id.longValue());
                employee.setUsername((String) getCellValue(employeeRow.getCell(1)));
                String dateString = (String) getCellValue(employeeRow.getCell(2));
                employee.setInputtime(simpleDateFormat.parse(dateString));
                employee.setTel((String) getCellValue(employeeRow.getCell(3)));
                employee.setEmail((String) getCellValue(employeeRow.getCell(4)));
                employeeService.saveEmployee(employee);
            }

        }catch (Exception e){
            ajaxRes.setSuccess(false);
            ajaxRes.setMessage("上传失败");
            e.printStackTrace();
        }
        return ajaxRes;
    }

    private Object getCellValue(Cell cell){
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
        }
        return cell;
    }
}
